﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Exam.DAL
{
    public sealed class SQLHelper
    {
        static string connectionString = ConfigurationManager.AppSettings["SqlConnection"].ToString();
        #region 初始化SQLCommand对象
        /// <summary>
        /// 初始化SQLCommand对象
        /// </summary>
        /// <param name="cmd">要被初始化的SQLCommand对象</param>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="tran">事务</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">SQL脚本或者存储过程名或视图名</param>
        /// <param name="pars">参数</param>
        static void PrePareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction tran, CommandType cmdType, string cmdText, SqlParameter[] pars)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            if (tran != null) cmd.Transaction = tran;
            if (pars != null)
            {
                cmd.Parameters.AddRange(pars);
            }
        }
        #endregion

        #region 返回受影响的行数
        /// <summary>
        /// 
        /// 执行存储过程或SQL脚本返回影响的行数
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">脚本</param>
        /// <param name="paras">参数，可选</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                PrePareCommand(cmd, conn, null, cmdType, cmdText, paras);
                int result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return result;
            }
        }
        #endregion

        #region 返回读取器
        /// <summary>
        /// 返回一个读取器对象
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteGetReader(CommandType cmdType, string cmdText, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                PrePareCommand(cmd, conn, null, cmdType, cmdText, paras);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
        }
        #endregion

        #region 执行一个命令并返回第一列
        /// <summary>
        /// 执行一个命令并返回第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] paras)
        {
            using (SqlConnection conn=new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();
                PrePareCommand(cmd, conn, null, cmdType, cmdText, paras);
                object obj = cmd.ExecuteScalar();
                return obj;
            }
        }
        #endregion
    }
}
